<?php

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
$bar="Numberday of all staff";
        echo '<table class="tablebg" cellspacing="1" align="right" border="1" width="900px">';
        echo '<tr><td colspan=10 align=right class=bar>'.$bar.'</td></tr>';
        //echo '<tr><td colspan=9 align=right class=bar>Totals of results: '.$total_rows.'</td></tr>';
        //echo '<tr><td colspan=9 align=right class=pagination>'.$pagination.'</td></tr>';
        /*echo '
              <th>userid</th>';
        foreach ($query->result() as $row)
        {
            echo "<tr>
                        <td class=\"row1\">$row->userid</td>";
        }*/
        echo '
              <th>fullname</th>
              <th>OT_Weekday</th>
              <th>OT_Saturday</th>
              <th>OT_Sunday</th>
              <th>overnight</th>
              <th>djdj</th>
               <th>djdj</th>
              <th>workedday</th>
              <th>workingday</th>
              <th>timesat</th>
              <th>timesun</th>';
        //$numberday=0;
        $hour=0;
        $minute=0;
        $hour1=0;
        $minute1=0;
        
        $hour2=0;
        $minute2=0;
        $hour3=0;
        $minute3=0;
        
        $hour4=0;
        $minute4=0;
        $hour5=0;
        $minute5=0;
        
        $overnight=0;
        $numbersunday=0;
        $numbersaturday=0;
        $labworkingday=0;
        $labworkedday=0;
        foreach ($query->result() as $row)
        {
            echo "<tr>
                       ";
            $sql="select * from ewt_users where id=$row->userid";
            $query1=$this->db->query($sql);
            foreach ($query1->result() as $row1)
            {
            echo "<td class=\"row1\">$row1->fullname</td>";      
            }
            //echo "<td class=\"row1\">$row->numberday</td>";
            $sql2="select * from ewt_works where userid=$row->userid and starttime between '$fromdate' and '$todate' and starttime<>endtime AND (
weekday = 'Monday'
OR weekday = 'Tuesday'
OR weekday = 'Wednesday'
OR weekday = 'Thursday'
OR weekday = 'Firday'
)
AND substr( overtime, 4, 2 ) !=0";
            $query2=$this->db->query($sql2);
            $a=0;$b=0;$c=0;$d=0;$e=0;$f=0;$g=0;
            $i=0;$k=0;
            $gio=0;
            $phut=0;
            $gio1=0;
            $phut1=0;
            foreach($query2->result() as $row2)
            {
                $phut=$phut+substr($row2->overtime,3,2);
                $gio=$gio+substr($row2->overtime,0,2);
                $phut1=$phut%60;
                $gio1=(int)($phut/60)+$gio;
            }
            $i=$gio1.':'.$phut1;
            
            echo "<td class=\"row1\">$i</td>";
            
            
            $sql8="select * from ewt_works where userid=$row->userid and starttime between '$fromdate' and '$todate' and starttime<>endtime AND ( weekday = 'Saturday') AND substr( overtime, 4, 2 ) !=0";
            $query8=$this->db->query($sql8);
            
            $gio2=0;
            $phut2=0;
            $gio3=0;
            $phut3=0;
            foreach($query8->result() as $row8)
            {
                $phut2=$phut2+substr($row8->overtime,3,2);
                $gio2=$gio2+substr($row8->overtime,0,2);
                $phut3=$phut2%60;
                $gio3=(int)($phut2/60)+$gio2;
            }
            $i1=$gio3.':'.$phut3;
            
            echo "<td class=\"row1\">$i1</td>";
            
            $sql9="select * from ewt_works where userid=$row->userid and starttime between '$fromdate' and '$todate' and starttime<>endtime AND ( weekday = 'Sunday') AND substr( overtime, 4, 2 ) !=0";
            $query9=$this->db->query($sql9);
            
            $gio4=0;
            $phut4=0;
            $gio5=0;
            $phut5=0;
            foreach($query9->result() as $row9)
            {
                $phut4=$phut4+substr($row9->overtime,3,2);
                $gio4=$gio4+substr($row9->overtime,0,2);
                $phut5=$phut4%60;
                $gio5=(int)($phut4/60)+$gio4;
            }
            $i2=$gio5.':'.$phut5;
            
            echo "<td class=\"row1\">$i2</td>";
            $sql7="select count(*) as numberdayovernight from ewt_works where userid=$row->userid and starttime between '$fromdate' and '$todate' and starttime<>endtime and ((substr(starttime,12,2)>='21' and substr(endtime,12,2)<'24') or (substr(starttime,12,2)>'00' and substr(starttime,12,2)<'05'))";
            $query7=$this->db->query($sql7);
            foreach ($query7->result() as $row7)
            {
            echo "<td class=\"row1\">$row7->numberdayovernight</td>";
                
            }
            $sql3="select count(*) as numbersunday from ewt_works where userid=$row->userid and starttime between '$fromdate' and '$todate' and starttime<>endtime and weekday='Sunday'";
            $query3=$this->db->query($sql3);
            foreach ($query3->result() as $row3)
            {
                echo "<td class=\"row1\">$row3->numbersunday</td>";
            }
             $sql4="select count(*) as numbersaturday from ewt_works where userid=$row->userid and starttime between '$fromdate' and '$todate' and starttime<>endtime and weekday='Saturday'";
            $query4=$this->db->query($sql4);
            foreach ($query4->result() as $row4)
            {
                echo "<td class=\"row1\">$row4->numbersaturday</td>";
            }
             $sql5="select count(*) as numbersatall from ewt_works where userid=$row->userid and starttime between '$fromdate' and '$todate'  and weekday='Saturday'";
            $query5=$this->db->query($sql5);
            foreach ($query5->result() as $row5)
            {
                //echo "<td class=\"row1\">$row5->numbersatall</td>";
            }
            $sql6="select count(*) as numbersundaya from ewt_works where userid=$row->userid and starttime between '$fromdate' and '$todate' and weekday='Sunday'";
            $query6=$this->db->query($sql6);
            foreach ($query6->result() as $row6)
            {
                //echo "<td class=\"row1\">$row3->numbersunday</td>";
            }
            $workedday=$row->numberday-$row3->numbersunday+$row5->numbersatall-$row4->numbersaturday;
            //$workedday=$row->numberday+$row5->numbersatall;
            //echo "<td class=\"row1\">$workingday</td>";
            //$k=$workingday*8;
            echo "<td class=\"row1\">$workedday</td>";
              $sql30="select count(distinct substr(starttime,1,10)) as totalday from ewt_works where starttime between '$fromdate' and '$todate'";
            $query30=$this->db->query($sql30);
            foreach ($query30->result() as $row30)
            {
                
            }
            $sql31="select count(distinct substr(starttime,1,10)) as totalsunday from ewt_works where weekday='Sunday' and starttime between '$fromdate' and '$todate'";
            $query31=$this->db->query($sql31);
            foreach ($query31->result() as $row31)
            {
                
            }
            $workingday=$row30->totalday-$row31->totalsunday;
            echo "<td class=\"row1\">$workingday</td>";
            //$numberday=$numberday+$row->numberday;
            $minute=$minute+$phut1;
            $hour=$hour+$gio1;
            $minute1=$minute%60;
            $hour1=(int)($minute/60)+$hour;
            
            $minute2=$minute2+$phut3;
            $hour2=$hour2+$gio3;
            $minute3=$minute2%60;
            $hour3=(int)($minute2/60)+$hour2;
            
            $minute4=$minute4+$phut5;
            $hour4=$hour4+$gio5;
            $minute5=$minute4%60;
            $hour5=(int)($minute4/60)+$hour4;
            
            $overnight=$overnight+$row7->numberdayovernight;
            $numbersunday=$numbersunday+$row3->numbersunday;
            $numbersaturday=$numbersaturday+$row4->numbersaturday;
            //$labworkingday=$labworkingday+$workingday;
            $labworkedday=$labworkedday+$workedday;
            $sql10="select * from ewt_works where userid=$row->userid and starttime between '$fromdate' and '$todate' and starttime<>endtime and weekday='Saturday'";
            $query10=$this->db->query($sql10);
            $hoursatturday=0;
            $minutesaturday=0;
            $hoursatturday1=0;
            $minutesaturday1=0;
            foreach($query10->result() as $row10)
            {
                echo substr($row10->starttime,14,2);
                 if(substr($row10->starttime,14,2)>substr($row10->endtime,14,2))
                {
                    $phutthu7=substr($row10->endtime,14,2)+60-substr($row10->starttime,14,2);
                    if((substr($row10->endtime,11,12)<=13||substr($row10->starttime,11,12)>=13)&&substr($row10->starttime,11,12)!=0)
                    $giothu7=substr($row10->endtime,11,2)-substr($row10->starttime,11,2)-1;
                    else {
                    $giothu7=substr($row10->endtime,11,2)-substr($row10->starttime,11,2)-2;
                    }
                }
                else
                {
                    $phutthu7=substr($row10->endtime,14,2)-substr($row10->starttime,14,2);
                    if((substr($row10->endtime,11,12)<=13||substr($row10->starttime,11,12)>=13)&&substr($row10->starttime,11,12)!=0)
                    $giothu7=substr($row10->endtime,11,2)-substr($row10->starttime,11,2);
                    else
                    {
                    $giothu7=substr($row10->endtime,11,2)-substr($row10->starttime,11,2)-1;
                    }
                }
                
                $minutesaturday=$minutesaturday+$phutthu7;
                $hoursatturday=$hoursatturday+$giothu7;
                $minutesaturday1=$minutesaturday%60;
                $hoursatturday1=(int)($minutesaturday/60)+$hoursatturday;
            }
            if($minutesaturday1<$phut3)
            {
                $minutesatday=$minutesaturday1+60-$phut3;
                $hoursatday=$hoursatturday1-1-$gio3;
            }
            else
            {
                $minutesatday=$minutesaturday1-$phut3;
                $hoursatday=$hoursatturday1-$gio3;
            }
            echo "<td class=\"row1\">$hoursatday:$minutesatday</td>";
            $sql11="select * from ewt_works where userid=$row->userid and starttime between '$fromdate' and '$todate' and starttime<>endtime and weekday='Sunday'";
            $query11=$this->db->query($sql11);
            $hoursunday=0;
            $minutesunday=0;
            $hoursunday1=0;
            $minutesunday1=0;
            foreach($query11->result() as $row11)
            {
                if(substr($row11->starttime,14,2)>substr($row11->endtime,14,2))
                {
                    $phutcn=substr($row11->endtime,14,2)+60-substr($row11->starttime,14,2);
                    if((substr($row11->endtime,11,12)<=13||substr($row11->starttime,11,12)>=13)&&substr($row11->starttime,11,12)!=0)
                    $giocn=substr($row11->endtime,11,2)-substr($row11->starttime,11,2)-1;
                    else
                        $giocn=substr($row11->endtime,11,2)-substr($row11->starttime,11,2)-2;
                }
                else
                {
                    $phutcn=substr($row11->endtime,14,2)-substr($row11->starttime,14,2);
                    if((substr($row11->endtime,11,12)<=13||substr($row11->starttime,11,12)>=13)&&substr($row11->starttime,11,12)!=0)
                    $giocn=substr($row11->endtime,11,2)-substr($row11->starttime,11,2);
                    else
                        $giocn=substr($row11->endtime,11,2)-substr($row11->starttime,11,2)-1;
                }
                
                $minutesunday=$minutesunday+$phutcn;
                $hoursunday=$hoursatturday+$giocn;
                $minutesunday1=$minutesunday%60;
                $hoursunday1=(int)($minutesunday/60)+$hoursunday;
            }
            if($minutesunday1<$phut5)
            {
                $minutesuday=$minutesunday1+60-$phut5;
                $hoursuday=$hoursunday1-1-$gio5;
            }
            else
            {
                $minutesuday=$minutesunday1-$phut5;
                $hoursuday=$hoursunday1-$gio5;
            }
            echo "<td class=\"row1\">$hoursuday:$minutesuday</td>";
            
            //$hourtime=$hourtime+$k;
        }
        
        echo '</table>';
        echo '<table class="tablebg" cellspacing="1" align="right" border="1" width="900px">';
         echo '<tr><td colspan=2 align=right class=bar>Detail days of Lab</td></tr>';
        echo "      
                    <tr>
                        <td class=\"row1\">Total OT_Weekday of lab</td>
                        <td class=\"row1\">$hour1:$minute1</td>
                    </tr>
                    <tr>
                        <td class=\"row1\">Total OT_Saturday of lab</td>
                        <td class=\"row1\">$hour3:$minute3</td>
                    </tr>
                    <tr>
                        <td class=\"row1\">Total OT_Sunday of lab</td>
                        <td class=\"row1\">$hour5:$minute5</td>
                    </tr>
                    <tr>
                        <td class=\"row1\">Total day overnight of lab</td>
                        <td class=\"row1\">$overnight</td>
                    </tr>
                    <tr>
                        <td class=\"row1\">Total work saturday of lab</td>
                        <td class=\"row1\">$numbersaturday</td>
                    </tr>
                    <tr>
                        <td class=\"row1\">Total work sunday of lab</td>
                        <td class=\"row1\">$numbersunday</td>
                    </tr>
                     <tr>
                        <td class=\"row1\">Total workedday of lab</td>
                        <td class=\"row1\">$labworkedday</td>
                    </tr>
         ";
        echo '</table>';
?>
